package git.soulbgm.stats.gov.cn;


import git.soulbgm.utils.IoUtil;
import git.soulbgm.utils.StringUtil;
import git.soulbgm.utils.okhttp.OkHttpUtil;
import org.jsoup.Connection;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;

import java.util.ArrayList;
import java.util.List;

/**
 * 国家统计局行政区划数据爬取应用程序
 *
 * @author SoulBGM
 * @date 2022/08/26 10:28
 */
public class StatsApplication {

    private static final String INDEX_PATH = "http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2021/";

    public static void main(String[] args) throws Exception {
        List<AdministrativeDivision> adList = getAdministrativeDivisions();
        System.out.println(toSql(adList));
    }

    /**
     * 获得行政区划
     *
     * @return {@link List}<{@link AdministrativeDivision}>
     * @throws Exception 异常
     */
    public static List<AdministrativeDivision> getAdministrativeDivisions() throws Exception {
        List<AdministrativeDivision> adList = new ArrayList<>();
        Connection connect = Jsoup.connect(INDEX_PATH);
        connect.header("User-Agent", "Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/68.0.3440.106 Safari/537.36");
        Connection.Response rs = connect.execute();
        // 转换为Dom树
        Document doc = Jsoup.parse(rs.body());
        Elements et = doc.select("tr[class='provincetr']");
        for (Element e : et) {
            Elements tag = e.getElementsByTag("td");
            for (Element e2 : tag) {
                Elements aTag = e2.getElementsByTag("a");
                String name = aTag.html();
                String href = aTag.attr("href");
                String code = href.substring(0, href.lastIndexOf("."));
                AdministrativeDivision ad = new AdministrativeDivision(formatName(name), code);
                addChild(href, name, ad.child);
                adList.add(ad);
            }
        }
        return adList;
    }

    public static void addChild(String href, String parentName, List<AdministrativeDivision> adList) throws Exception {
        Connection connect = Jsoup.connect(INDEX_PATH + href);
        connect.header("User-Agent", "Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/68.0.3440.106 Safari/537.36");
        Connection.Response rs = connect.execute();
        // 转换为Dom树
        Document doc = Jsoup.parse(rs.body());
        Elements et = doc.select("tr[class='citytr']");
        if (et.isEmpty()) {
            et = doc.select("tr[class='countytr']");
        }
        for (Element e : et) {
            Elements tag = e.getElementsByTag("a");
            String nextHref = tag.attr("href");
            Element first = tag.first();
            String code = first.html().substring(0, 4);
            Element last = tag.last();
            String name = last.html();
            if ("市辖区".equals(name)) {
                name = parentName;
            }
            if ("自治区直辖县级行政区划".equals(name)) {
                addChild(nextHref, name, adList);
            } else {
                adList.add(new AdministrativeDivision(formatName(name), code));
            }
        }
    }

    public static String formatName(String name) {
        return name.replaceAll("<br>", "");
    }

    public static String toSql(List<AdministrativeDivision> adList) {
        StringBuilder dictTypeTableSql = new StringBuilder();
        StringBuilder dictDataTableSql = new StringBuilder();
        dictTypeTableSql.append("INSERT INTO dict_type (id, dict_name, dict_type, use_status, remark, create_time, update_time) VALUES\n");
        dictDataTableSql.append("INSERT INTO dict_data (id, dict_sort, dict_label, dict_value, dict_type, is_default, use_status, create_time, update_time, remark) VALUES\n");
        int dictTypeIndex = 52;
        int dictDataIndex = 233;
        dictTypeTableSql.append(StringUtil.format("({}, '行政区划', 'administrative_division', 1, '行政区划列表', sysdate, sysdate),\n", ++dictTypeIndex));
        for (int i = 0; i < adList.size(); i++) {
            AdministrativeDivision ad = adList.get(i);
            dictTypeTableSql.append(StringUtil.format("({}, '行政区划-{}', 'administrative_division_{}', 1, '{}下属区划列表', sysdate, sysdate),\n", ++dictTypeIndex, ad.name, ad.code, ad.name));
            dictDataTableSql.append(StringUtil.format("({}, {}, '{}', '{}', 'administrative_division', {}, 1, sysdate, sysdate, '行政区划 {}'),\n", ++dictDataIndex, i + 1, ad.name, ad.code, i == 0 ? 1 : 0, ad.name));
            List<AdministrativeDivision> child = ad.child;
            int size = child.size();
            for (int j = 0; j < size; j++) {
                AdministrativeDivision c = child.get(j);
                dictDataTableSql.append(StringUtil.format("({}, {}, '{}', '{}', 'administrative_division_{}', {}, 1, sysdate, sysdate, '行政区划 {}'),\n", ++dictDataIndex, j + 1, c.name, c.code, ad.code, j == 0 ? 1 : 0, c.name));
            }
        }

        return dictTypeTableSql + "\n\n" + dictDataTableSql;
    }

}
